
import 'dart:io';
import 'package:flutter/cupertino.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sciolto/POJO/SongList.dart';
import 'package:sqflite/sqflite.dart';

class DBController{
  static DBController _instance;
  factory DBController() => _instance ??= DBController._internal();
  DBController._internal();

  Database _database;

  Future<Database> get database async {
    if(_database == null){
      _database = await _initDB();
      return _database;
    }else if(_database.isOpen){
      return _database;
    }else{
      _database = await _initDB();
      return _database;
    }
  }

  Future<Database> _initDB() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, 'UserDB.db');
    return await openDatabase(
        path,
        version: 1,
        onOpen: (db){
    },
        onCreate:(db, version){
          print('数据库当前版本 : $version');
        }
    );
  }

  Future initExecute(String sql) async {
    final db= await database;
    print(db.isOpen);
    await db.execute(sql);
  }

  Future<int> insert(String tableName, Map<String, dynamic> map) async {
    final db = await database;
    return await db.insert(tableName, map);
  }

  Future<int> rawInsert(String sql) async {
    final db = await database;
    return await db.rawInsert(sql);
  }

  Future<List<Map<String, dynamic>>> query(String tableName, String where, List<dynamic> whereArgs) async {
    final db = await database;
    return await db.query(
      tableName,
      where: where,
      whereArgs: whereArgs
    );
  }

  Future<List<Map<String, dynamic>>> queryAll(String tableName) async {
    final db = await database;
    return await db.query(tableName);
  }

  Future<List<Map<String, dynamic>>> rawQuery(String sql) async {
    final db = await database;
    return await db.rawQuery(sql);
  }

  Future<int> delete(String tableName, String where, List<dynamic> whereArgs) async {
    final db = await database;
    return await db.delete(
      tableName,
      where: where,
      whereArgs: whereArgs
    );
  }

  Future<int> update(String tableName,Map<String, dynamic> map, String where, List<dynamic> whereArgs) async {
    final db = await database;
    return await db.update(
      tableName,
      map,
      where: where,
      whereArgs: whereArgs
    );
  }

  removeAll(String tableName) async {
    final db = await database;
    db.delete(tableName);
  }

  Future close() async {
    final db = await database;
    db.close();
  }

}

final String initSqlForSongList = '''
    create table if not exists songlist(
      id integer primary key autoincrement,
      list_name text not null,
      image_path text not null,
      hits integer not null,
      resource_type integer not null,
      introduce text not null
    )
  ''';

final String initSqlForSong = '''
    create table if not exists song(
      id integer primary key autoincrement,
      mp3_file_name text not null
    )
  ''';

final String initSqlForFeeling = '''
    create table if not exists feeling(
      id integer primary key autoincrement,
      parent_song_id integer not null,
      content text not null,
      time text not null
    )
  ''';

final String initSqlForBelonging = '''
    create table if not exists belonging(
      sid integer not null,
      lid integer not null
    )
  ''';

/// 与数据库控制器[DBController]交互的类的父类[TableConnector]
/// API：
///   基本操作
///   [checkIfHasCreateTable]确定表是否已创建，没创建则需要创建
///   [deleteAll]删除表内数据
///   [close]关闭连接
///
///   默认的操作
///   [defaultQuery]默认查询
///   [defaultInsert]默认插入
///   [defaultDelete]默认删除
///   [defaultUpdate]默认更新
///
///   自定义操作
///   [customQuery]自定义复杂查询
///   [customInsert]自定义复杂插入
///   [customDelete]自定义复杂删除
///   [customUpdate]自定义复杂更新
///
class TableConnector{

  /// 确定要查询的表是否已经创建，如果没有创建则需要创建
  /// 每次单例初始化时一定要调用此方法
  String _tableName;
  DBController _controller;

  TableConnector(this._tableName){
    _controller = DBController();
    checkIfHasCreateTable();
  }

  Future checkIfHasCreateTable() async {
    _controller.initExecute(initSqlForSongList);
    _controller.initExecute(initSqlForSong);
    _controller.initExecute(initSqlForFeeling);
    _controller.initExecute(initSqlForBelonging);
  }

  Future close() => _controller.close();

  Future<int> defaultDelete(String where, List<dynamic> whereArgs) =>
      _controller.delete(_tableName, where, whereArgs);

  Future<int> defaultInsert(Map<String, dynamic> map) =>
      _controller.insert(_tableName, map);

  Future<List<Map<String, dynamic>>> defaultQuery(
      String where,
      List<dynamic> whereArgs
      ) => _controller.query(_tableName, where, whereArgs);

  Future<List<Map<String, dynamic>>> defaultQueryAll() =>
      _controller.queryAll(_tableName);

  Future<int> defaultUpdate(
      Map<String, dynamic> map,
      String where,
      List<dynamic> whereArgs
      ) => _controller.update(_tableName, map, where, whereArgs);

  deleteAll() => _controller.removeAll(_tableName);

  Future<List<Map<String, dynamic>>> customQuery(SongList songList) {
    assert(songList.id != null);
    String sql = '''
      select song.id, song.mp3_file_name
      from song, belonging, songlist 
      where song.id = belonging.sid 
        and belonging.lid = songlist.id
        and songlist.list_name = '${songList.listName}' 
    ''';
    return _controller.rawQuery(sql);
  }

   Future<int> customInsert({@required int lid, @required int sid}){
      assert(lid != null && sid != null);
      String sql = '''
        insert into belonging values($sid, $lid)
      ''';
      return _controller.rawInsert(sql);
   }

  // // ignore: missing_return
  // Future<int> customDelete(){}
  //
  // // ignore: missing_return
  // Future<int> customUpdate(){}
}

/// 表连接器的工厂类
///
/// 返回对应表的连接器
/// usage:
/// ```dart
///   TableConnector connector = TableConnectorFactory.connectWithFeelingTable();
/// ```
class TableConnectorFactory{

  static TableConnector connectWithFeelingTable() => TableConnector("feeling");

  static TableConnector connectWithSongTable() => TableConnector("song");

  static TableConnector connectWithSonglistTable() => TableConnector("songlist");

  static TableConnector connectWithBelongingTable() => TableConnector("belonging");

}
